//
//  YNOrgTree.m
//  Messenger
//
//  Created by OC on 15/1/15.
//  Copyright (c) 2015年 李健. All rights reserved.
//
//  组织机构实体类
//

#import "YNOrgTree.h"
#import "FMDatabase.h"
#import "FMDatabaseQueue.h"
#import "FMDatabaseAdditions.h"
#import "YNIMTool.h"
#import "YNIMFrameworkMacro.h"

@implementation YNOrgTree

-(void)setValue:(id)value forUndefinedKey:(NSString *)key{
    if (!value)
    {
        if ([key isEqualToString:@"ordId"]){
            self.ordId=0;
        }else if ([key isEqualToString:@"orgType"]){
            self.orgType=0;
        }else if ([key isEqualToString:@"removeTag"]){
            self.removeTag=0;
        }else if ([key isEqualToString:@"userCount"]){
            self.userCount=0;
        }else if ([key isEqualToString:@"onlineUserCount"]){
            self.onlineUserCount=0;
        }
    }
}

+ (NSString *)orgNo
{
    return @"orgNo";
}

+ (NSString *)parentOrgNo
{
    return @"parentOrgNo";
}

+ (NSString *)orgName
{
    return @"orgName";
}

+ (NSString *)orgType
{
    return @"orgType";
}

+ (NSString *)ordId
{
    return @"ordId";
}

+ (NSString *)removeTag
{
    return @"removeTag";
}

+ (NSString *)profile
{
    return @"profile";
}

+ (NSString *)tableName
{
    return @"OrganizationTree";
}

- (NSArray *)insertParam
{
    return @[[YNIMTool nilFilter:self.orgNo],
             [YNIMTool nilFilter:self.orgName],
             [YNIMTool nilFilter:self.parentOrgNo],
             [YNIMTool nilFilter:self.profile],
             [NSNumber numberWithInt: self.orgType],
             [NSNumber numberWithInt:self.ordId],
             [NSNumber numberWithInt: self.removeTag]];
}

- (NSArray *)updateParam
{
    return @[[YNIMTool nilFilter:self.orgName],
             [YNIMTool nilFilter:self.parentOrgNo],
             [YNIMTool nilFilter:self.profile],
             [NSNumber numberWithInt: self.orgType],
             [NSNumber numberWithInt:self.ordId],
             [NSNumber numberWithInt: self.removeTag],
             [YNIMTool nilFilter:self.orgNo]];
}

//创建组织机构表
+ (BOOL)createInDatabase:(FMDatabase *)db
{
    if ([db tableExists:[YNOrgTree tableName]])
    {
        return YES;
    }
    
    
    NSString *createTableSql = [NSString stringWithFormat:@"%@%@(%@ varchar(36) NOT NULL PRIMARY KEY UNIQUE, %@ varchar(36), %@ varchar(100), %@ varchar(100), %@ INTEGER, %@ INTEGER, %@ INTEGER)",CREATE_TABLE,
                                [YNOrgTree tableName],
                                [YNOrgTree orgNo],
                                [YNOrgTree orgName],
                                [YNOrgTree parentOrgNo],
                                [YNOrgTree profile],
                                [YNOrgTree orgType],
                                [YNOrgTree ordId],
                                [YNOrgTree removeTag]];
    
    BOOL result = [db executeUpdate:createTableSql];
    
    return result;
}

//插入组织机构信息
- (BOOL)insertInDatabase:(FMDatabase *)db
{
    NSString *fieldString = [NSString stringWithFormat:@"%@,%@,%@,%@,%@,%@,%@",
                             [YNOrgTree orgNo],
                             [YNOrgTree orgName],
                             [YNOrgTree parentOrgNo],
                             [YNOrgTree profile],
                             [YNOrgTree orgType],
                             [YNOrgTree ordId],
                             [YNOrgTree removeTag]];
    
    NSString *insertOrgTreeSql = [NSString stringWithFormat:@"%@%@ (%@) VALUES (?,?,?,?,?,?,?)",INSERT_TABLE,[YNOrgTree tableName],fieldString];
    BOOL result = [db executeUpdate:insertOrgTreeSql withArgumentsInArray:[self insertParam]];
    
    return result;
}
//获取组织机构数据库数据条数
+ (NSUInteger)getOrgTreeDataCountInDatabase:(FMDatabase *)db{
    NSUInteger count=[db intForQuery:[NSString stringWithFormat:@"SELECT count(*) FROM %@",[YNOrgTree tableName]]];
    return count;
}

//根据orgNo查询组织机构信息
- (NSArray *)queryByOrgNoInDatabase:(FMDatabase *)db
{
    NSString *queryOrgTreeSql = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ = ? AND removeTag = 0 ORDER BY ordId ASC",
                                 [YNOrgTree tableName],
                                 [YNOrgTree orgNo]];
    FMResultSet *rs = [db executeQuery:queryOrgTreeSql,self.orgNo];
    
    NSMutableArray *orgTreeArray = [NSMutableArray array];
    
    while ([rs next])
    {
        YNOrgTree *orgTree = [[YNOrgTree alloc] init];
        if (![YNIMTool isEmptyDictionary:[rs resultDictionary]]) {
            [orgTree setValuesForKeysWithDictionary:[rs resultDictionary]];
        }
        
        [orgTreeArray addObject:orgTree];
        
    }
    
    [rs close];
    
    return orgTreeArray;
}

//20169-9jc修改-根据orgNo查询组织机构信息
- (NSArray *)logicQueryByOrgNoInDatabase:(FMDatabase *)db
{
    NSString *queryOrgTreeSql = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ = ? ORDER BY ordId ASC",
                                 [YNOrgTree tableName],
                                 [YNOrgTree orgNo]];
    FMResultSet *rs = [db executeQuery:queryOrgTreeSql,self.orgNo];
    
    NSMutableArray *orgTreeArray = [NSMutableArray array];
    
    while ([rs next])
    {
        YNOrgTree *orgTree = [[YNOrgTree alloc] init];
        if (![YNIMTool isEmptyDictionary:[rs resultDictionary]]) {
            [orgTree setValuesForKeysWithDictionary:[rs resultDictionary]];
        }
        
        [orgTreeArray addObject:orgTree];
        
    }
    
    [rs close];
    
    return orgTreeArray;
}

//根据parentOrgNo查询组织机构信息
+ (NSArray *)queryByParentOrgNo:(NSString *)parentOrgNo database:(FMDatabase *)db
{
    //    NSString *orderByStr = @"DESC";
    
    //2016-11-7jc修改，人员排序
    NSString *orderByStr = @"ASC";
    
    NSString *queryOrgTreeSql = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ = ? AND removeTag = 0 ORDER BY ordId %@",
                                 [YNOrgTree tableName],
                                 [YNOrgTree parentOrgNo],orderByStr];
    
    //    debugLog(@"queryOrgTreeSql=%@",queryOrgTreeSql);
    
    
    FMResultSet *rs = [db executeQuery:queryOrgTreeSql,parentOrgNo];
    NSMutableArray *orgTreeArray = [NSMutableArray array];
    
    while ([rs next])
    {
        YNOrgTree *orgTree = [[YNOrgTree alloc] init];
        if (![YNIMTool isEmptyDictionary:[rs resultDictionary]]) {
            [orgTree setValuesForKeysWithDictionary:[rs resultDictionary]];
        }
        
        [orgTreeArray addObject:orgTree];
    }
    
    [rs close];
    
    return orgTreeArray;
}


//根据parentOrgNo查询组织机构信息
+ (NSArray *)queryAllByParentOrgNo:(NSString *)parentOrgNo database:(FMDatabase *)db
{
    
    //2016-11-7jc修改，人员排序
    NSString *orderByStr = @"ASC";
    
    NSString *queryOrgTreeSql = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ = ? AND removeTag = 0 ORDER BY ordId %@",
                                 [YNOrgTree tableName],
                                 [YNOrgTree parentOrgNo],orderByStr];
    
    FMResultSet *rs = [db executeQuery:queryOrgTreeSql,parentOrgNo];
    NSMutableArray *orgTreeArray = [NSMutableArray array];
    
    while ([rs next])
    {
        YNOrgTree *orgTree = [[YNOrgTree alloc] init];
        if (![YNIMTool isEmptyDictionary:[rs resultDictionary]]) {
            [orgTree setValuesForKeysWithDictionary:[rs resultDictionary]];
        }
        
        [orgTreeArray addObject:orgTree];
    }
    
    [rs close];
    
    return orgTreeArray;
}

//根据parentOrgNo查询联系人中组织机构信息
+ (NSArray *)queryControllerByParentOrgNo:(NSString *)parentOrgNo getAllStudent:(BOOL)allStudent database:(FMDatabase *)db
{
    //2016-11-7jc修改，人员排序
    NSString *orderByStr = @"ASC";
    NSString *queryOrgTreeSql = @"";
    if (allStudent) {
        queryOrgTreeSql = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ = ? AND %@ IN(2,3,4) AND removeTag = 0 ORDER BY ordId %@",
        [YNOrgTree tableName],
        [YNOrgTree parentOrgNo],[YNOrgTree orgType],orderByStr];
    }else{
        queryOrgTreeSql = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ = ? AND %@ IN(2,3) AND removeTag = 0 ORDER BY ordId %@",
        [YNOrgTree tableName],
        [YNOrgTree parentOrgNo],[YNOrgTree orgType],orderByStr];
    }
    
    FMResultSet *rs = [db executeQuery:queryOrgTreeSql,parentOrgNo];
    NSMutableArray *orgTreeArray = [NSMutableArray array];
    
    while ([rs next])
    {
        YNOrgTree *orgTree = [[YNOrgTree alloc] init];
        if (![YNIMTool isEmptyDictionary:[rs resultDictionary]]) {
            [orgTree setValuesForKeysWithDictionary:[rs resultDictionary]];
        }
        
        [orgTreeArray addObject:orgTree];
    }
    
    [rs close];
    
    return orgTreeArray;
    
}

//根据parentOrgNoS查询联系人中组织机构信息-可分页查询
+ (NSArray *)queryControllerByOrgNoStringS:(NSString*)parentOrgNoS getAllStudent:(BOOL)allStudent limitStart:(NSUInteger)start limitLength:(NSUInteger)length database:(FMDatabase *)db
{
    //2016-11-7jc修改，人员排序
    NSString *orderByStr = @"ASC";
    NSString *queryOrgTreeSql =@"";
    //正在分页查询数据
    if (length<=0) {
        if (allStudent) {
            queryOrgTreeSql= [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ in (%@) AND %@ IN(2,3,4) AND removeTag = 0 ORDER BY ordId %@",
            [YNOrgTree tableName],
            [YNOrgTree parentOrgNo],parentOrgNoS,[YNOrgTree orgType],orderByStr];
        }else{
            queryOrgTreeSql= [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ in (%@) AND %@ IN(2,3) AND removeTag = 0 ORDER BY ordId %@",
            [YNOrgTree tableName],
            [YNOrgTree parentOrgNo],parentOrgNoS,[YNOrgTree orgType],orderByStr];
        }
        
    }else{
        if (allStudent) {
            queryOrgTreeSql= [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ in (%@) AND %@ IN(2,3,4) AND removeTag = 0 ORDER BY ordId %@ LIMIT %d , %d",
            [YNOrgTree tableName],
            [YNOrgTree parentOrgNo],parentOrgNoS,[YNOrgTree orgType],orderByStr,start,length];
        }else{
            queryOrgTreeSql= [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ in (%@) AND %@ IN(2,3) AND removeTag = 0 ORDER BY ordId %@ LIMIT %d , %d",
            [YNOrgTree tableName],
            [YNOrgTree parentOrgNo],parentOrgNoS,[YNOrgTree orgType],orderByStr,start,length];
        }
        
    }
    FMResultSet *rs = [db executeQuery:queryOrgTreeSql];
    NSMutableArray *orgTreeArray = [NSMutableArray array];
    
    while ([rs next])
    {
        YNOrgTree *orgTree = [[YNOrgTree alloc] init];
        if (![YNIMTool isEmptyDictionary:[rs resultDictionary]]) {
            [orgTree setValuesForKeysWithDictionary:[rs resultDictionary]];
        }
        
        [orgTreeArray addObject:orgTree];
    }
    
    [rs close];
    
    return orgTreeArray;
    
}

//根据parentOrgNo查询组织机构中组织机构信息
+ (NSArray *)queryOrganizationByParentOrgNo:(NSString *)parentOrgNo database:(FMDatabase *)db
{
    //2016-11-7jc修改，人员排序
    NSString *orderByStr = @"ASC";
    
    //2018-1-21JC修改，老师-联系人-同事-去掉实习活动，实习活动添加到联系人-学生里面。
    //    NSString *queryOrgTreeSql = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ = ? AND %@ NOT IN(2,3) AND removeTag = 0 ORDER BY ordId %@",
    NSString *queryOrgTreeSql = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ = ? AND %@ NOT IN(2,3,4) AND removeTag = 0 ORDER BY ordId %@",
                                 [YNOrgTree tableName],
                                 [YNOrgTree parentOrgNo],[YNOrgTree orgType],orderByStr];
    
    FMResultSet *rs = [db executeQuery:queryOrgTreeSql,parentOrgNo];
    NSMutableArray *orgTreeArray = [NSMutableArray array];
    
    while ([rs next])
    {
        YNOrgTree *orgTree = [[YNOrgTree alloc] init];
        if (![YNIMTool isEmptyDictionary:[rs resultDictionary]]) {
            [orgTree setValuesForKeysWithDictionary:[rs resultDictionary]];
        }
        
        [orgTreeArray addObject:orgTree];
    }
    
    [rs close];
    
    return orgTreeArray;
}

//根据parentOrgNo查询组织机构中组织机构信息-只查-老师-实习活动
+ (NSArray *)queryOrganizationInternshipByParentOrgNo:(NSString *)parentOrgNo database:(FMDatabase *)db
{
    //2016-11-7jc修改，人员排序
    NSString *orderByStr = @"ASC";
    NSString *queryOrgTreeSql = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ = ? AND %@  IN(4) AND removeTag = 0 ORDER BY ordId %@",
                                 [YNOrgTree tableName],
                                 [YNOrgTree parentOrgNo],[YNOrgTree orgType],orderByStr];
    
    FMResultSet *rs = [db executeQuery:queryOrgTreeSql,parentOrgNo];
    NSMutableArray *orgTreeArray = [NSMutableArray array];
    
    while ([rs next])
    {
        YNOrgTree *orgTree = [[YNOrgTree alloc] init];
        if (![YNIMTool isEmptyDictionary:[rs resultDictionary]]) {
            [orgTree setValuesForKeysWithDictionary:[rs resultDictionary]];
        }
        
        [orgTreeArray addObject:orgTree];
    }
    
    [rs close];
    
    return orgTreeArray;
}





//根据关键字查询OrgTree
+ (NSArray *)queryOrgTreeByKeywords:(NSString *)keyWords database:(FMDatabase *)db orgType:(NSInteger)orgType
{
    NSString *queryOrgTreeByKeywords = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE removeTag = 0 AND %@ like '%%%@%%'escape '/' ORDER BY ordId DESC",[YNOrgTree tableName],[YNOrgTree orgName],keyWords];
    if (orgType==2) {
        queryOrgTreeByKeywords = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE removeTag = 0 AND  %@ NOT IN(2,3) AND %@ like '%%%@%%'escape '/' ORDER BY ordId DESC",[YNOrgTree tableName],[YNOrgTree orgType],[YNOrgTree orgName],keyWords];
    }
    
    
    FMResultSet *rs = [db executeQuery:queryOrgTreeByKeywords];
    
    NSMutableArray *tempOrgTreeArray = [NSMutableArray array];
    
    while ([rs next])
    {
        YNOrgTree *orgTree = [[YNOrgTree alloc] init];
        if (![YNIMTool isEmptyDictionary:[rs resultDictionary]]) {
            [orgTree setValuesForKeysWithDictionary:[rs resultDictionary]];
        }
        
        [tempOrgTreeArray addObject:orgTree];
    }
    
    [rs close];
    
    return tempOrgTreeArray;
    
}


//更新组织机构信息
- (BOOL)updateInDatabase:(FMDatabase *)db
{
    NSString *updateOrgTreeSql = [NSString stringWithFormat:@"UPDATE %@ SET %@ = ?, %@ = ?, %@ = ?, %@ = ?, %@ = ?, %@ = ? WHERE %@ = ?",
                                  [YNOrgTree tableName],
                                  [YNOrgTree orgName],
                                  [YNOrgTree parentOrgNo],
                                  [YNOrgTree profile],
                                  [YNOrgTree orgType],
                                  [YNOrgTree ordId],
                                  [YNOrgTree removeTag],
                                  [YNOrgTree orgNo]];
    
    BOOL result = [db executeUpdate:updateOrgTreeSql withArgumentsInArray:[self updateParam]];
    return result;
}

/**
 *  @brief  遍历组织机构的所有上级机构
 *
 *  @param parentOrgNo 父节点
 *  @param db
 *
 *  @return
 */
+ (NSMutableArray *)queryParentOrgByParentOrgNo:(NSString *)parentOrgNo database:(FMDatabase *)db
{
    NSString *queryParentOrgNo = parentOrgNo;
    
    NSMutableArray *parentOrgArray = [NSMutableArray array];
    
    while (![queryParentOrgNo isEqualToString:@"0"])
    {
        NSArray *tempArray = [YNOrgTree queryOrgTreeByParentOrgNo:queryParentOrgNo database:db];
        YNOrgTree *orgTree = [tempArray lastObject];
        queryParentOrgNo = [NSString stringWithFormat:@"%@",orgTree.parentOrgNo];
        [parentOrgArray addObject:orgTree];
    }
    return parentOrgArray;
}

//根据parentOrgNo查询组织机构信息
+ (NSArray *)queryOrgTreeByParentOrgNo:(NSString *)parentOrgNo database:(FMDatabase *)db
{
    NSString *orderByStr = @"DESC";
    
    NSString *queryOrgTreeSql = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ = ? AND removeTag = 0 ORDER BY ordId %@",
                                 [YNOrgTree tableName],
                                 [YNOrgTree orgNo],orderByStr];
    
    FMResultSet *rs = [db executeQuery:queryOrgTreeSql,parentOrgNo];
    NSMutableArray *orgTreeArray = [NSMutableArray array];
    
    while ([rs next])
    {
        YNOrgTree *orgTree = [[YNOrgTree alloc] init];
        if (![YNIMTool isEmptyDictionary:[rs resultDictionary]]) {
            [orgTree setValuesForKeysWithDictionary:[rs resultDictionary]];
        }
        
        [orgTreeArray addObject:orgTree];
        
    }
    
    [rs close];
    
    return orgTreeArray;
}



//删除表中所有数据
+ (BOOL)deleteAllOrgTreeDataInDatabase:(FMDatabase *)db
{
    NSString *deleteAllOrgTreeDataSql = [NSString stringWithFormat:@"DELETE FROM %@ ",[YNOrgTree tableName]];
    
    BOOL result = [db executeUpdate:deleteAllOrgTreeDataSql];
    
    return result;
}

//2016-9-9jc修改，逻辑删除表中所有数据
+(BOOL)logicDeleteAllOrgTreeDataInDatabase:(FMDatabase *)db
{
    
    
    NSString *updateUserTreeRelationSql = [NSString stringWithFormat:@"UPDATE %@ SET %@ = 1",
                                           [YNOrgTree tableName],
                                           [YNOrgTree removeTag]];
    
    BOOL result = [db executeUpdate:updateUserTreeRelationSql];
    return result;
    
    
}

//2016-9-9jc修改，判断组织机构是否存在
- (BOOL)logicIsOrgTreeExistInDatabase:(FMDatabase *)db
{
    NSArray *orgTreeArray = [self logicQueryByOrgNoInDatabase:db];
    if (orgTreeArray.count > 0)
    {
        return YES;
    }
    else
    {
        return NO;
    }
}

//判断组织机构是否存在
- (BOOL)isOrgTreeExistInDatabase:(FMDatabase *)db
{
    NSArray *orgTreeArray = [self queryByOrgNoInDatabase:db];
    if (orgTreeArray.count > 0)
    {
        return YES;
    }
    else
    {
        return NO;
    }
}

@end
